using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;

namespace DataSync.DataAccess.Attendance
{
    public class AttendanceDataAccess
    {
        private MySqlConnection _MySqlConnection;
        private MySqlCommand _MySqlCommand;
        private MySqlDataAdapter _MySqlDataAdapter;        
        private string _ConnectionString;
        private string _QueryString;

        public DataTable GetAttendances(int serverIdentification)
        {
            var dataTable = new DataTable();

            switch (serverIdentification)
            {
                case 1:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringFp"].ToString();
                    break;
                case 2:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSocial"].ToString();
                    break;
                case 3:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringHotel"].ToString();
                    break;
                case 4:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMamie"].ToString();
                    break;
                case 5:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMeca"].ToString();
                    break;
                default:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
                    break;
            }

            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString = "SELECT * FROM attendance";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);            
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable GetAttendances(DateTime fromDate, DateTime toDate, int departmentId)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString =
                "SELECT" +
                "	employee.id, " +
                "	employee.name, " +
                "   department.department, " +
                "   position.title, " +
                "   attendance.time_in, " +
                "   attendance.time_out, " +
                "   attendance.shift, " +
                "   attendance.reason, " +
                "   employee.sex " +
                "FROM" +
                "   employee " +
                "   left join position on employee.position = position.id" +
                "	left join department on position.department = department.id" +
                "	left join attendance on employee.id = attendance.employee_id" +
                "	left join working_shift on attendance.shift = working_shift.id " +
                "WHERE" +
                "   dateAt BETWEEN '" + fromDate.ToString("yyyy-MM-dd") + "'" +
                "   AND '" + toDate.ToString("yyyy-MM-dd") + "' " +
                "   AND UCASE(employee.employee_status) = 'ACTIVE'" +
                "   AND department.id = " + departmentId + " " + 
                "ORDER BY" +
                "   employee.name";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable GetPublicHoliday(DateTime dateTime)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString = "SELECT * From holiday WHERE date = '" + dateTime.ToString("yyyy-MM-dd") + "'";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;            
        }

        public DataTable GetAuthorizedLeave(DateTime dateTime, int staffId)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();
            _QueryString = "SELECT * From leavemanagement WHERE (employee_id = " + staffId + ") AND (startdate <= '" + dateTime.ToString("yyyy-MM-dd") + "') AND (finishdate >= '" + dateTime.ToString("yyyy-MM-dd") + "') AND (etat = 1)";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable CalculateTimeInMorning(DateTime dateTime, int staffId, string shiftColumn)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();
            _QueryString =
                "SELECT" +
                "	ADDTIME(attendance.time_in, - working_shift." + shiftColumn + ") as ans " +
                "FROM" +
                "	working_shift" +
                "   INNER JOIN employee on employee.shift = working_shift.id" +
                "	INNER JOIN attendance on attendance.employee_id = employee.id " +
                "WHERE" +
                "	UCASE(employee.employee_status) = 'ACTIVE'" +
                "   AND attendance.employee_id = " + staffId + 
                "	AND attendance.time_in = (" +
                "       SELECT " +
                "           MIN(time_in) " +
                "       FROM " +
                "           attendance " +
                "       WHERE " +
                "           attendance.employee_id = " + staffId +
                "           AND attendance.time_in < '12:00:00'" +
                "           AND attendance.dateAt = '" + dateTime.ToString("yyyy-MM-dd") + "')";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        //public DataTable GetAttendanceMorningOnly(DateTime dateTime, int staffId)
        //{
        //    var dataTable = new DataTable();

        //    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
        //    _MySqlConnection = new MySqlConnection(_ConnectionString);
        //    _MySqlConnection.Open();
        //    _QueryString =
        //        "SELECT" +
        //        "	id " +
        //        "FROM" +
        //        "	attendance " +
        //        "WHERE" +
        //        "    employee_id = " + staffId + 
        //        "    AND dateAt = '" + dateTime.ToString("yyyy-MM-dd") + "'" +  
        //        "    AND time_in < '12:00:00'" + 
        //        "    AND time_out is null";
        //    _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
        //    _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
        //    _MySqlDataAdapter.Fill(dataTable);
        //    _MySqlConnection.Close();
        //    return dataTable;
        //}

        public DataTable CalculateTimeOutMorning(DateTime dateTime, int staffId, string shiftColumn)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();
            _QueryString =
                "SELECT" +
                "	ADDTIME(attendance.time_out, - working_shift." + shiftColumn + ") as ans " +
                "FROM" +
                "	working_shift" +
                "   INNER JOIN employee on employee.shift = working_shift.id" +
                "	INNER JOIN attendance on attendance.employee_id = employee.id " +
                "WHERE" +
                "	UCASE(employee.employee_status) = 'ACTIVE'" +
                "   AND attendance.employee_id = " + staffId +
                "	AND attendance.time_out = (" +
                "       SELECT " +
                "           MAX(time_out) " +
                "       FROM " +
                "           attendance " +
                "           INNER JOIN working_shift on attendance.shift = working_shift.id" +
                "       WHERE " +
                "           attendance.employee_id = " + staffId +
                "           AND attendance.time_in < '12:00:00'" +
                "           AND attendance.dateAt = '" + dateTime.ToString("yyyy-MM-dd") + "'" +
                "           AND attendance.time_out < working_shift." + shiftColumn + ")";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable CalculateTimeInAfternoon(DateTime dateTime, int staffId, string shiftColumn)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();
            _QueryString =
                "SELECT" +
                "	ADDTIME(attendance.time_in, - working_shift." + shiftColumn + ") as ans " +
                "FROM" +
                "	working_shift" +
                "   INNER JOIN employee on employee.shift = working_shift.id" +
                "	INNER JOIN attendance on attendance.employee_id = employee.id " +
                "WHERE" +
                "	UCASE(employee.employee_status) = 'ACTIVE'" +
                "   AND attendance.employee_id = " + staffId +
                "	AND attendance.time_in = (" +
                "       SELECT " +
                "           MIN(time_in) " +
                "       FROM " +
                "           attendance " +
                "       WHERE " +
                "           attendance.employee_id = " + staffId +
                "           AND attendance.time_in >= '12:00:00'" +
                "           AND attendance.dateAt = '" + dateTime.ToString("yyyy-MM-dd") + "')";
            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public DataTable CalculateTimeOutAfternoon(DateTime dateTime, int staffId, string shiftColumn)
        {
            var dataTable = new DataTable();

            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();
            _QueryString =
                "SELECT" +
                "	ADDTIME(attendance.time_out, -working_shift." + shiftColumn + ") as ans " +
                "FROM" +
                "	working_shift" +
                "   INNER JOIN employee on employee.shift = working_shift.id" +
                "	INNER JOIN attendance on attendance.employee_id = employee.id " +
                "WHERE" +
                "	UCASE(employee.employee_status) = 'ACTIVE'" +
                "   AND attendance.employee_id = " + staffId +
                "   AND attendance.time_out = (" +
                "       SELECT " +
                "           MAX(time_out) " +
                "       FROM " +
                "           attendance " +
                "           INNER JOIN working_shift on attendance.shift = working_shift.id" +
                "       WHERE " +
                "           attendance.employee_id = " + staffId +
                "           AND attendance.time_in >= '12:00:00'" +
                "	        AND attendance.time_out >= attendance.time_in" +
                "           AND attendance.dateAt = '" + dateTime.ToString("yyyy-MM-dd") + "'" +
                "           AND attendance.time_out < working_shift." + shiftColumn + ")";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlDataAdapter = new MySqlDataAdapter(_MySqlCommand);
            _MySqlDataAdapter.Fill(dataTable);
            _MySqlConnection.Close();
            return dataTable;
        }

        public bool InsertAttendance(DataRow dataRow)
        {
            _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString =
                "INSERT INTO attendance(employee_id, dateAt, time_in, time_out, shift, reason) VALUES(" +
                dataRow["employee_id"] + ", " +
                "'" + dataRow["dateAt"] + "', " +
                (!(dataRow["time_in"] is DBNull) ?  "'" + dataRow["time_in"] + "'" : "null") + ", " + 
                (!(dataRow["time_out"] is DBNull) ? "'" + dataRow["time_out"] + "'" : "null") + ", " + 
                dataRow["shift"] + ", " +
                "'" + dataRow["reason"] + "')";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlCommand.ExecuteNonQuery();
            _MySqlConnection.Close();
            return true;
        }

        public bool DeleteAttendances(int serverIdentification)
        {
            switch (serverIdentification)
            {
                case 1:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringFp"].ToString();
                    break;
                case 2:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSocial"].ToString();
                    break;
                case 3:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringHotel"].ToString();
                    break;
                case 4:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMamie"].ToString();
                    break;
                case 5:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringMeca"].ToString();
                    break;
                default:
                    _ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringCentral"].ToString();
                    break;
            }
            _MySqlConnection = new MySqlConnection(_ConnectionString);
            _MySqlConnection.Open();

            _QueryString ="DELETE FROM attendance";

            _MySqlCommand = new MySqlCommand(_QueryString, _MySqlConnection);
            _MySqlCommand.ExecuteNonQuery();
            _MySqlConnection.Close();
            return true;            
        }
    }
}